
using System.Web.UI.HtmlControls;
using System;
using System.Configuration;
using System.Drawing;
using System.Data;
using System.Web.UI.WebControls;
using Microsoft.VisualBasic;
using System.Collections;
using System.Web.UI;
using System.Web;


using System.IO;
using System.Text;
//using com.ddsc.database;
using DDSC.OracleADO;
using com.ddsc.common;
using SMAS;
using System.Resources;

namespace smasb2b
{
	public class CTMD0060 : BasePage
	{
		
		
		
#region  Web Form 設計工具產生的程式碼
		
		//此為 Web Form 設計工具所需的呼叫。
		[System.Diagnostics.DebuggerStepThrough()]private void InitializeComponent()
		{
			this.ID = "lblCTMD0060";
			
		}
		protected System.Web.UI.WebControls.Label lblCTMD0060;
		protected System.Web.UI.WebControls.Label lblIN_OUT_DATE;
		protected ddscControlLibrary.DateCtrl txtIN_OUT_DATE1;
		protected ddscControlLibrary.DateCtrl txtIN_OUT_DATE2;
		protected ddscControlLibrary.ClientButtonCtrl btnSEARCH;
		protected ddscControlLibrary.ClientButtonCtrl btnFIND;
		protected System.Web.UI.HtmlControls.HtmlTable gridBar;
		protected System.Web.UI.HtmlControls.HtmlInputButton BtnFirst;
		protected System.Web.UI.HtmlControls.HtmlInputButton BtnPrev;
		protected System.Web.UI.HtmlControls.HtmlInputButton BtnNext;
		protected System.Web.UI.HtmlControls.HtmlInputButton BtnLast;
		protected System.Web.UI.HtmlControls.HtmlInputButton BtnGoto;
		protected System.Web.UI.WebControls.Label lblSHEET_NO;
		protected ddscControlLibrary.InputBoxCtrl txtSHEET_NO1;
		protected ddscControlLibrary.InputBoxCtrl txtSHEET_NO2;
		protected System.Web.UI.WebControls.Label lblCTM_APPLY_NO;
		protected ddscControlLibrary.InputBoxCtrl txtCTM_APPLY_NO1;
		protected ddscControlLibrary.InputBoxCtrl txtCTM_APPLY_NO2;
		protected System.Web.UI.WebControls.Label lblCODE1;
		protected ddscControlLibrary.InputBoxCtrl txtCODE1;
		protected ddscControlLibrary.InputBoxCtrl txtCODE2;
		protected System.Web.UI.WebControls.Label lblCUSTOMS_TYPE;
		protected System.Web.UI.WebControls.DropDownList drpCUSTOMS_TYPE;
		protected System.Web.UI.WebControls.Label lblCTM_APPLY_NO1;
		protected System.Web.UI.WebControls.Label lblIN_OUT_DATE1;
		protected System.Web.UI.WebControls.Label lblCODE_1;
		protected ddscControlLibrary.InputBoxCtrl txtCODE_1;
		protected ddscControlLibrary.InputBoxCtrl txtCODE_2;
		
		//注意: 下列預留位置宣告是 Web Form 設計工具需要的項目。
		//請勿刪除或移動它。
		private System.Object designerPlaceholderDeclaration;
		
		private void Page_Init(System.Object sender, System.EventArgs e)
		{
			//CODEGEN: 此為 Web Form 設計工具所需的方法呼叫
			//請勿使用程式碼編輯器進行修改。
			InitializeComponent();
		}
		
#endregion
		public CTMD0060()
		{
			// 設定log4net之格式
			log4net.Config.DOMConfigurator.Configure(new System.IO.FileInfo("log4netAppender.xml"));
		}
		FunctionHandler FunctionHandler = new FunctionHandler();
		private void Page_Load(System.Object sender, System.EventArgs e)
		{
			//在這裡放置使用者程式碼以初始化網頁
		}
		//查詢(search)

		//public DataSet Query(ddscDataSet p_ddscDt)
        public DataSet Query(com.ddsc.common.ddscDataSet p_ddscDt)
		{
			// 宣告回傳之Dataset
			DataSet dsReturn = default(DataSet);
			
			// 宣告傳入之DataSet
			DataSet ds = default(DataSet);
			
			try
			{
				// 取得前端傳入之dataset
				ds = p_ddscDt.getDataSet();
				
				// logger,開始log
				//If logger.IsInfoEnabled Then
				//    logger.Info(ds.DataSetName & " :: start")
				//End If
				
				// 處理 前端page 傳來的 DataSet 資料
				DataTable dt = ds.Tables["CTM_CUSTOMS_TRANS"];
				string CODE1 = System.Convert.ToString(dt.Rows[1]["CODE1"].ToString());
				string CODE2 = System.Convert.ToString(dt.Rows[1]["CODE1"].ToString());
				string CUSTOMS_TYPE = System.Convert.ToString(dt.Rows[1]["CUSTOMS_TYPE"].ToString());
				string SHEET_NO1 = System.Convert.ToString(dt.Rows[1]["SHEET_NO1"].ToString());
				string SHEET_NO2 = System.Convert.ToString(dt.Rows[1]["SHEET_NO2"].ToString());
				string APPLY_NO1 = System.Convert.ToString(dt.Rows[1]["CTM_APPLY_NO1"].ToString());
				string APPLY_NO2 = System.Convert.ToString(dt.Rows[1]["CTM_APPLY_NO2"].ToString());
				string IN_OUT_DATE1 = System.Convert.ToString(dt.Rows[1]["IN_OUT_DATE1"].ToString());
				string IN_OUT_DATE2 = System.Convert.ToString(dt.Rows[1]["IN_OUT_DATE2"].ToString());
				
				// 查詢資料內容
				StringBuilder sb = new StringBuilder();
				
				sb.Append("SELECT /*FIRST_ROWS*/A.SHEET_NO,A.IN_OUT_DATE,A.APPLY_NO,A.TRANS_NO,A.STORE_NO,CUSTOMS_TYPE FROM ");
				sb.Append("     (SELECT /*FIRST_ROWS*/ DISTINCT SHEET_NO,IN_OUT_DATE,APPLY_NO,TRANS_NO,STORE_NO,SUBSTR(SHEET_NO,1,2) CUSTOMS_TYPE ");

                //Tony : 1.修改SQL語法使用參數，不組字串
				//sb.Append("     FROM CTM_CUSTOMS_TRANS_MASTER WHERE SHEET_NO LIKE \'" + CUSTOMS_TYPE + "%\' ");
                sb.Append("     FROM CTM_CUSTOMS_TRANS_MASTER WHERE SHEET_NO LIKE :CUSTOMS_TYPE ");

                //Tony : 2.設定動態參數物件
                ArrayList m_arrParam = new ArrayList();
                dbAccess dba = new dbAccess();
                m_arrParam.Add(dba.GetOracleParam(":CUSTOMS_TYPE", CUSTOMS_TYPE+"%"));

				if (SHEET_NO1 != "")
				{
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //sb.Append(" AND SHEET_NO BETWEEN \'" + SHEET_NO1 + "\' AND \'" + SHEET_NO2 + "\' ");
                    sb.Append(" AND SHEET_NO BETWEEN :SHEET_NO1 AND :SHEET_NO2 ");

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":SHEET_NO1", SHEET_NO1));
                    m_arrParam.Add(dba.GetOracleParam(":SHEET_NO2", SHEET_NO2));
				}
				
				if (APPLY_NO1 != "")
				{
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //sb.Append(" AND APPLY_NO BETWEEN \'" + APPLY_NO1 + "\' AND \'" + APPLY_NO2 + "\' ");
					sb.Append(" AND APPLY_NO BETWEEN :APPLY_NO1 AND :APPLY_NO2 ");

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":APPLY_NO1", APPLY_NO1));
                    m_arrParam.Add(dba.GetOracleParam(":APPLY_NO2", APPLY_NO2));
				}
				
				if (IN_OUT_DATE1 != "")
				{
                    //Tony : 1.修改SQL語法使用參數，不組字串
					//sb.Append(" AND IN_OUT_DATE BETWEEN \'" + IN_OUT_DATE1 + "\' AND \'" + IN_OUT_DATE2 + "\' ");
                    sb.Append(" AND IN_OUT_DATE BETWEEN :IN_OUT_DATE1 AND :IN_OUT_DATE2 ");

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":IN_OUT_DATE1", IN_OUT_DATE1));
                    m_arrParam.Add(dba.GetOracleParam(":IN_OUT_DATE2", IN_OUT_DATE2));
				}
				
				sb.Append(")A");
				
				if (CODE1 != "")
				{
					sb.Append("     ,(SELECT DISTINCT SHEET_NO,STORE_NO FROM CTM_CUSTOMS_TRANS_DETAILS ");
                    //Tony : 1.修改SQL語法使用參數，不組字串
					//sb.Append("         WHERE CODE BETWEEN \'" + CODE1 + "\' AND \'" + CODE2 + "\')B ");
                    sb.Append("         WHERE CODE BETWEEN :CODE1 AND :CODE2)B ");

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":CODE1", CODE1));
                    m_arrParam.Add(dba.GetOracleParam(":CODE2", CODE2));

					sb.Append("WHERE A.SHEET_NO=B.SHEET_NO AND A.STORE_NO=B.STORE_NO ");
				}
				
				// 組合 sql command 的 order by 語法
				if (!(dt.Rows[1]["sort"] == null) && dt.Rows[1]["sort"].ToString().Trim().Length > 0)
				{
					sb.Append(" Order By " + dt.Rows[1]["sort"].ToString());
				}
				
				// 查詢資料總筆數之 sql command
				StringBuilder sb_count = new StringBuilder();
				sb_count.Append("Select Count(*) As row_total_count");

				sb_count.Append(" From (" + sb.ToString() + ")");
                

				// DataTable Name
				string dtName = "";
				dtName = "CTM_CUSTOMS_TRANS";
				
				// Sql command
				string sqlCommands = "";
				sqlCommands = sb.ToString();
				
				// maximun record
				int maxrecord = 0;
				maxrecord = System.Int32.Parse(System.Convert.ToString(dt.Rows[1]["maxrecord"]));
				
				// absolute position
				int position = 0;
				position = System.Int32.Parse(System.Convert.ToString(dt.Rows[1]["position"]));
				
				// create database access object
				//dbAccess dba = new dbAccess();
				DataSet dsCount = default(DataSet);
				int intCount = 0;
				// 計算資料總筆數
                //Tony : 3這邊的sb_count因為沒設定參數,所以不給
                dsCount = dba.runExecuteQuery("CTM_CUSTOMS_TRANS_Count", sb_count.ToString());

				if (dsCount.Tables[0].Rows[0]["ReturnCode"] == "000")
				{
					intCount = int.Parse(System.Convert.ToString(dsCount.Tables["CTM_CUSTOMS_TRANS_count"].Rows[0]["row_total_count"]));
				}
				
				// 計算第一筆之位置
				if (intCount < position)
				{
					//上次位置小於總筆數，需重新計算位置(ex:流覽資料時，其他user 將資料刪除)
					if (intCount < maxrecord)
					{
						// 總筆數(intCount)小於一頁，從第0筆開始顯示
						position = 0;
					}
					else
					{
						// 總筆數(intCount)大於一頁且小於上次位置，則以整數除法除以(每頁筆數maxrecord)取其商，
						// 再乘以每頁筆數(maxrecord)即可得後最後一頁第一筆之位置
						position = System.Convert.ToInt32((intCount / maxrecord) * maxrecord);
					}
				}
				
				// 呼叫database.dll進行access動作
                //Tony : 3.修改執行方法Overload
                //dsReturn = dba.runExecuteQuery(dtName, sqlCommands, maxrecord, position);
                dsReturn = dba.runExecuteQuery(dtName, sqlCommands, maxrecord, position, m_arrParam);
				// 將總筆數加入Table("info")
				dsReturn.Tables["info"].Rows[0]["RowCount"] = intCount;
				// 將Position欄位加入Table("info")
				dsReturn.Tables["info"].Columns.Add("Position");
				// 加入Position 位置
				dsReturn.Tables["info"].Rows[0]["Position"] = position;
				// debug logger
				//If logger.IsDebugEnabled Then
				//    logger.Debug("sql command :: " & sb.ToString())
				//    logger.Debug("sql command :: " & sb_count.ToString())
				//    logger.Debug("maximun record :: " & maxrecord.ToString())
				//    logger.Debug("absolute position :: " & position.ToString())
				//End If
				
				// logger - result
				//If logger.IsDebugEnabled Then
				//    logTransResult(dsReturn)
				//End If
				
			}
			catch (Exception ex)
			{
				// create return object (DataSet)
				dsReturn = new DataSet(ds.DataSetName.ToString());
				dsReturn.Tables.Add(createErrorTable(ex.Message));
				
				// logger
				//If logger.IsErrorEnabled Then
				//    logger.Error("error :: " & ex.ToString())
				//End If
				
				//' logger
				//If logger.IsInfoEnabled Then
				//    logger.Info(ds.DataSetName & " :: end")
				//End If
				//Throw ex
			}
			
			return dsReturn;
			
		}
		private DataTable createErrorTable(string mError)
		{
			dbReturnInfo dbInfo = new dbReturnInfo("info");
			dbInfo.AddNewRow();
			dbInfo.setReturnDataValue("F", System.Convert.ToString(999), 0, mError);
			return dbInfo.getReturnDataTable();
		}
		
		//log與db交易結果
		private void logTransResult(DataSet ds)
		{
			DataSet tempDs = default(DataSet);
			try
			{
				tempDs = new DataSet(ds.DataSetName);
				tempDs.Tables.Add(ds.Tables["info"].Copy());
				// logger.Debug(tempDs.GetXml())
			}
			catch (Exception)
			{
				// logger.Error("error :: " & ex.ToString())
			}
			finally
			{
				if (!(tempDs == null))
				{
					tempDs.Dispose();
					tempDs = null;
				}
			}
		}
		
	}
	
}
